package cn.thornbird.orgsync.repository;

import cn.thornbird.orgsync.entity.City;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface CityRepository extends BaseRepository<City>, JpaRepository<City, String>,
        JpaSpecificationExecutor<City> {

    City findFirstByNameIgnoreCaseAndParentIdIsNull(String name);

    City findFirstByParentIdAndNameIgnoreCase(String parentId, String name);

    List<City> findByIdIn(List<String> ids);

    @Query(nativeQuery = true, value = "select * from cities where parent_id is null and deleted_at is null " +
            "order by city_number asc")
    List<City> findProvinces();

    @Query(nativeQuery = true, value = "select * from cities where parent_id is not null and deleted_at is null " +
            "order by parent_id asc, city_number asc")
    List<City> findCities();

    @Query(nativeQuery = true, value = "select * from cities where parent_id = :parentId and deleted_at is null " +
            "order by city_number asc")
    List<City> findCitiesByParentId(String parentId);

    @Query(nativeQuery = true, value = "select id from companies where city_id = :cityId and deleted_at is null " +
            "union select id from companies where city_id in (select id from cities where parent_id = :cityId) " +
            "and deleted_at is null")
    List<Object> findCityIdsByCityId(String cityId);

    @Modifying
    @Query(nativeQuery = true, value = "update cities set order_number = :parentOrderNumber + city_number " +
            "where parent_id = :parentId")
    void updateOrderNumbers(String parentId, Integer parentOrderNumber);

    @Modifying
    @Query("update #{#entityName} e set e.deletedAt = CURRENT_TIMESTAMP where e.parentId = ?1")
    void logicDeleteByParentId(String parentId);

}
